package com.tuanzi.loan.business.repository.family.impl;

import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.jooq.DSLContext;
import org.jooq.Record;
import org.jooq.RecordMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.Pageable;
import org.springframework.stereotype.Repository;

import com.google.common.collect.Lists;
import com.tuanzi.loan.business.repository.family.UserMobileExportRepository;
import com.tuanzi.loan.vo.request.family.UserMobileExportSearchRequest;
import com.tuanzi.loan.vo.response.family.UserMobileExportSearchResponse;
import com.tuanzi.server.loan_base.util.DateUtils;
import com.tuanzi.server.loan_base.util.PhoneNumNideUtil;

/**
 * 用户手机号导出Repository处理
 * 
 * @author Xumiaohang 2017/11/14
 *
 */
@Repository
public class UserMobileExportRepositoryImpl implements
		UserMobileExportRepository {

	private static final String REGIST = "0"; // 注册用户
	private static final String RECORD = "1"; // 登记未注册用户

	@Autowired
	private DSLContext dslContext;
	
	public Page<UserMobileExportSearchResponse> list(
			UserMobileExportSearchRequest searchRequest, Pageable pageable) {

		Map<String,Object> map = getPageData(searchRequest, pageable.getOffset(), pageable.getPageSize(), false);
		//总记录数
		Long totalCount = (Long) map.get("totalCount");
		//分页数据
		List<UserMobileExportSearchResponse> pageList = (List<UserMobileExportSearchResponse>) map.get("list");
		return new PageImpl<UserMobileExportSearchResponse>(pageList, pageable, totalCount);

	}

	@Override
	public List<UserMobileExportSearchResponse> getExportData(
			UserMobileExportSearchRequest searchRequest, int offset,
			int pageSize) {

		Map<String,Object> map = getPageData(searchRequest, offset, pageSize, true);
		return (List<UserMobileExportSearchResponse>) map.get("list");
		
	}
	
	
	public Map<String,Object> getPageData(UserMobileExportSearchRequest searchRequest, int offset, int pageSize, boolean isExport){
		
		List<Object> paramList = Lists.newArrayList();
		StringBuilder sql = new StringBuilder();
		// 总记录数sql
		String countSql = null;
		//存放返回数据的集合
		Map<String,Object> returnMap=new HashMap<String,Object>();
		
		String registerSql = "SELECT rr.cdate AS recordTime, u.activedate AS registerTime, ra.activity_type_name AS activityType,"
				+ "rr.activity_id AS activityId, p.name AS productId, c.channel_name AS channelId,rr.platform, u.mobnum AS userId, bt.type_name AS blacklist ";

		String recordSql = "SELECT rr.cdate AS recordTime, ra.activity_type_name AS activityType,"
				+ "rr.activity_id AS activityId, p.name AS productId, c.channel_name AS channelId,rr.platform, rr.phone AS userId, bt.type_name AS blacklist ";

		// 默认初始查询当天的注册用户
		if (searchRequest.getStartRegTime() == null
				&& searchRequest.getStartRecTime() == null) {
			// 获取当天时间
			Calendar cal = Calendar.getInstance();
			String today = DateUtils.formatDateToYyyyMMdd(cal.getTime());

			sql = new StringBuilder(
					" FROM `comm_user` u LEFT JOIN `common_activity_register_record` rr ON u.mobnum = rr.phone ")
					.append(" LEFT JOIN `register_recommend_activity` ra ON rr.activity_type = ra.activity_type ")
					.append(" LEFT JOIN `user_blacklist` b ON u.mobnum=b.mobnum ")
					.append(" LEFT JOIN `blacklist_type` bt ON b.type=bt.type_code ")
					.append(" LEFT JOIN `comm_product_channel` c ON u.channel = c.channel_code ")
					.append(" LEFT JOIN `comm_product_prdid` p ON u.product_id = p.prdid ")
					.append(" WHERE u.status = 1 AND u.activedate = ? GROUP BY u.mobnum ORDER BY u.activedate DESC ");

			countSql = "SELECT COUNT(1) FROM ( " + registerSql + sql.toString()
					+ " ) AS uu ";
			paramList.add(today);
		}

		// 注册用户的查询
		String userType=searchRequest.getUserType();
		if (REGIST.equals(userType)) {
			sql = new StringBuilder(
					" FROM `comm_user` u LEFT JOIN `common_activity_register_record` rr ON u.mobnum = rr.phone ")
					.append(" LEFT JOIN `register_recommend_activity` ra ON rr.activity_type = ra.activity_type ")
					.append(" LEFT JOIN `user_blacklist` b ON u.mobnum=b.mobnum ")
					.append(" LEFT JOIN `blacklist_type` bt ON b.type=bt.type_code ")
					.append(" LEFT JOIN `comm_product_channel` c ON u.channel = c.channel_code ")
					.append(" LEFT JOIN `comm_product_prdid` p ON u.product_id = p.prdid ")
					.append(" WHERE u.status = 1 ");

			// 登记未注册用户的查询
		} else if (RECORD.equals(userType)) {
			sql = new StringBuilder(" FROM common_activity_register_record rr ")
					.append(" LEFT JOIN register_recommend_activity ra ON rr.activity_type = ra.activity_type ")
					.append(" LEFT JOIN user_blacklist b ON rr.phone=b.mobnum ")
					.append(" LEFT JOIN blacklist_type bt ON b.type=bt.type_code ")
					.append(" LEFT JOIN comm_product_channel c ON rr.channel = c.channel_code ")
					.append(" LEFT JOIN comm_product_prdid p ON rr.prdid = p.prdid ")
					.append(" WHERE NOT EXISTS(SELECT 1 FROM comm_user u WHERE rr.phone = u.mobnum ) ");
		}

		String activityType = searchRequest.getActivityType();
		if (StringUtils.isNotBlank(activityType)) {
			if (!"-1".equals(activityType)) {
				sql.append(" AND rr.activity_type=? ");
				paramList.add(activityType);
			} else {
				sql.append(" AND rr.activity_type IS NULL ");
			}
		}
		// 活动标识
		String activityId = searchRequest.getActivityId();
		if (StringUtils.isNotBlank(activityId)) {
			sql.append(" AND rr.activity_id=? ");
			paramList.add(activityId);
		}

		String product = searchRequest.getProduct();
		if (StringUtils.isNotBlank(product)) {
			// 判断是注册或登记未注册用户
			if (REGIST.equals(userType)) {
				sql.append(" AND u.product_id=? ");
			} else {
				sql.append(" AND rr.prdid=? ");
			}
			paramList.add(product);
		}

		Integer channel = searchRequest.getChannel();
		if (channel != null) {
			if (REGIST.equals(userType)) {
				sql.append(" AND u.channel=? ");
			} else {
				sql.append(" AND rr.channel=? ");
			}
			paramList.add(channel);
		}

		String platform = searchRequest.getPlatform();
		if (StringUtils.isNotBlank(platform)) {
			sql.append(" AND rr.platform=? ");
			paramList.add("0".equals(platform) ? "ios"
					: "android");
		}
		// 用户黑名单
		String blacklist = searchRequest.getBlacklist();
		if (StringUtils.isNotBlank(blacklist)) {
			if (!"-1".equals(blacklist)) {
				sql.append(" AND b.type=? ");
				paramList.add(blacklist);
			} else {
				sql.append(" AND b.type IS NULL ");
			}
		}

		// 屏蔽联登活动
		String unionLogin = searchRequest.getUnionLogin();
		if (StringUtils.isNotBlank(unionLogin)) {
			if (REGIST.equals(userType)) {
				sql.append(" AND NOT EXISTS (SELECT la.mobnum FROM loan_api_record la WHERE la.type = ? and u.mobnum = la.mobnum) ");
			} else {
				sql.append(" AND NOT EXISTS (SELECT la.mobnum FROM loan_api_record la WHERE la.type = ? and rr.phone = la.mobnum)");
			}
			paramList.add(unionLogin);
		}

		// 根据时间区间查询
		if (REGIST.equals(userType)) {
			sql.append(" AND u.activedate >= ?  AND u.activedate <= ? GROUP BY u.mobnum ORDER BY u.activedate DESC ");
			countSql = "SELECT COUNT(1) FROM ( " + registerSql + sql.toString()
					+ " ) AS uu ";

			paramList.add(DateUtils.formatDateToYyyyMMdd(searchRequest
					.getStartRegTime()));
			paramList.add(DateUtils.formatDateToYyyyMMdd(searchRequest
					.getEndRegTime()));
		} else if (RECORD.equals(userType)) {
			sql.append(" AND rr.cdate >= ? AND rr.cdate <= ? GROUP BY rr.phone ORDER BY rr.cdate DESC ");
			countSql = "SELECT COUNT(1) FROM ( " + recordSql + sql.toString()
					+ " ) AS uu ";

			paramList.add(DateUtils.formatDateToYyyyMMdd(searchRequest
					.getStartRecTime()));
			paramList.add(DateUtils.formatDateToYyyyMMdd(searchRequest
					.getEndRecTime()));
		}

		// 不是导出数据时，统计总记录数
		Long totalCount = null;
		if(!isExport){
			totalCount = dslContext.fetchOne(countSql, paramList.toArray()).into(
					Long.class);
		}

		// 添加limit分页查询条件
		String limitSql = sql.toString() + " LIMIT ?,? ";
		paramList.add(offset);
		paramList.add(pageSize);
		
		List<UserMobileExportSearchResponse> list = dslContext.fetch(
				(RECORD.equals(userType) ? recordSql : registerSql)
						+ limitSql, paramList.toArray()).map(
				new RecordMapper<Record, UserMobileExportSearchResponse>() {
					@Override
					public UserMobileExportSearchResponse map(Record record) {
						UserMobileExportSearchResponse response = new UserMobileExportSearchResponse();
						response.setRecordTime(record.get("recordTime", Date.class));
						try {// 注册用户
							response.setRegisterTime(record.get("registerTime", Date.class));
						} catch (Exception e) {// 登记未注册用户
							response.setRegisterTime(null);
						}
						response.setActivityType(record.get("activityType", String.class));
						response.setActivityId(record.get("activityId", String.class));
						response.setProductId(record.get("productId", String.class));
						response.setChannelId(record.get("channelId", String.class));
						response.setPlatform(record.get("platform", String.class));
						if (searchRequest.getStartRegTime() == null
								&& searchRequest.getStartRecTime() == null) {
							response.setUserType(REGIST); //初始设置为注册用户
						}else{
							response.setUserType(userType);
						}
						response.setBlacklist(record.get("blacklist", String.class));
						response.setUnionLogin(unionLogin);
						response.setUserId(PhoneNumNideUtil.hideNum(record.get("userId", String.class)));
						
						return response;
					}
				});
		
		returnMap.put("totalCount",totalCount); //总记录数
		returnMap.put("list",list); //分页数据
		return returnMap;
	}

	
}
